<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Student_Model extends CI_Model {
	
	public function get_student($username) {
		$query = "SELECT * FROM smb_students WHERE username = ?";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_student_by_id($id) {
		$query = "SELECT * FROM smb_students WHERE id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_student_by_id_for_sponsor($id, $sponsor) {
		$query = "
		SELECT DISTINCT 
			sst.id, sst.username, sst.name, sst.number, sst.year, sst.term, sst.major_id, sst.parent_income,
			sst.siblings, sst.gpa, sst.status 
		FROM 
			smb_sponsor_users AS ssu JOIN smb_scholarships AS ss ON ss.sponsor_id = ssu.id 
			JOIN smb_scholarship_applicants AS ssa ON ssa.id = ss.id 
			JOIN smb_students AS sst ON sst.username = ssa.username
		WHERE 
			ssa.status IN ('aktif', 'non-aktif') AND
			sst.id = ? AND ssu.username = ? ";
		$result = $this->db->query($query, array($id, $sponsor));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function list_year($database) {
		if ($database == FALSE) {
			$query = "SELECT DISTINCT year FROM smb_students WHERE year >= ? AND year <= ? ORDER BY year DESC";
			$result = $this->db->query($query, unserialize(RANGE_YEAR));
		} else {
			$query = "SELECT DISTINCT year FROM smb_students ORDER BY year DESC";
			$result = $this->db->query($query);
		}
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function list_major() {
		$query = "SELECT * FROM smb_majors ORDER BY major";
		$result = $this->db->query($query);
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_student($offset, $limit, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT ss.*, sm.major FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id 
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? LIMIT ?, ?";
		$result = $this->db->query($query, array($name, $year, $major, $gpa, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_student_for_sponsor($offset, $limit, $sponsor, $name, $year, $major, $scholarship) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		if ($scholarship == '') {
			$scholarship = '%';
		}
		$query = "
		SELECT 
			sst.id, sst.name, sst.year, sm.major, GROUP_CONCAT(ss.name SEPARATOR ', ') as scholarships 
		FROM 
			smb_sponsor_users AS ssu JOIN smb_scholarships AS ss ON ss.sponsor_id = ssu.id 
			JOIN smb_scholarship_applicants AS ssa ON ssa.id = ss.id 
			JOIN smb_students AS sst ON sst.username = ssa.username
			JOIN smb_majors AS sm ON sm.id = sst.major_id
		WHERE 
			ssa.status IN ('aktif', 'non-aktif') AND
			ssu.username = ? AND
			sst.name LIKE ? AND
			sst.year LIKE ? AND
			sst.major_id LIKE ? AND
			ss.id LIKE ?
		GROUP BY
			sst.id, sst.name, sst.year, sm.major
		LIMIT ?, ?";
		$result = $this->db->query($query, array($sponsor, $name, $year, $major, $scholarship, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_student($name, $year, $major) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id 
			WHERE ss.name LIKE ? and ss.year LIKE ? and ss.major_id LIKE ?";
		$result = $this->db->query($query, array($name, $year, $major));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_student_for_sponsor($sponsor, $name, $year, $major, $scholarship) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		if ($scholarship == '') {
			$scholarship = '%';
		}
		$query = "
		SELECT 
			COUNT(DISTINCT sst.id) AS sum 
		FROM 
			smb_sponsor_users AS ssu JOIN smb_scholarships AS ss ON ss.sponsor_id = ssu.id 
			JOIN smb_scholarship_applicants AS ssa ON ssa.id = ss.id 
			JOIN smb_students AS sst ON sst.username = ssa.username
			JOIN smb_majors AS sm ON sm.id = sst.major_id
		WHERE 
			ssa.status IN ('aktif', 'non-aktif') AND
			ssu.username = ? AND
			sst.name LIKE ? AND
			sst.year LIKE ? AND
			sst.major_id LIKE ?";
		$result = $this->db->query($query, array($sponsor, $name, $year, $major, $scholarship));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_histories($username) {
		$query = "SELECT * FROM smb_student_histories AS ssh JOIN smb_courses AS sc 
			ON ssh.id = sc.id WHERE ssh.username = ? ORDER BY ssh.term";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
}